package oop.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import oop.db.Database;

public class CategoryWithCount extends SimpleCategory {

	private int testCount;
	private int questionCount;
	
	public static List<CategoryWithCount> fetchByNameIgnoreCase(String name) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT cat_id, cat_name, COUNT(test_id), COUNT(ques_id) "
					+ "FROM (tblCategory LEFT OUTER JOIN tblTest ON cat_id = test_category) " 
							+ "LEFT OUTER JOIN tblQuestion ON cat_id = ques_category " 
					+ "WHERE UPPER(cat_name) LIKE UPPER(?) " 
					+ "GROUP BY cat_id " 
					+ "ORDER BY cat_name";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);
			rs = stmt.executeQuery();
			
			List<CategoryWithCount> categories = new ArrayList<CategoryWithCount>();
			while (rs.next()) {
				categories.add(new CategoryWithCount(rs.getInt(1), rs
						.getString(2), rs.getInt(3), rs.getInt(4)));
			}
			return categories;
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}


	public CategoryWithCount(int id, String name, int testCount,
			int questionCount) {
		super(id, name);
		this.testCount = testCount;
		this.questionCount = questionCount;
	}

	public int getTestCount() {
		return testCount;
	}

	public int getQuestionCount() {
		return questionCount;
	}

}
